<?php
class REGISTRATIONS{
    //general variables
	var $dbErrorMsg = "Failed set/get data from database (Class registrations): ";

	public $statusType = array(
		1=>array('Lämna in','#F8290C'),
		2=>array('Pågår','#F08423'),
		3=>array('Godkänt','#359F46'),
		4=>array('Ofullständig','#F08423')
	);	
    /** Function for getting the registration
     * @param  $registrationId
     * @return array
     */
    function getRegistration($registrationId=0){    	
        if($registrationId == 0){
        $sql="describe `".tblPrefix."registrations`";
    		$tmpres=MAIN::returnArray($sql);
    		$res=array();
    		foreach($tmpres as $r){
    			if($r['Field']=='id'){
    				$res[0][$r['Field']]=0;
    			}else{
    				$res[0][$r['Field']]='';
    			}    			
    		}
        }else{
			$sql="select `".tblPrefix."registrations`.*				
				from `".tblPrefix."registrations`				 			 
				 where `".tblPrefix."registrations`.`id`=".$registrationId;
			$res=MAIN::returnArray($sql);
        }
        return $res[0];
    }
	
	/** Function add/update association
	 * @param : posted data in DATA array, registrations ID
	 * @return: $registrationId 
	 */
    function setRegistration($id,$DATA){
    	$type=mysql_real_escape_string($DATA['txt_circle_type']);
    	$semester = mysql_real_escape_string($DATA['chk_period']);
    	switch($type){
			default:
				$members=array();
		    	/*ASSOCIATION*/
		    	$associationId = mysql_real_escape_string($DATA['txt_association_id']);
				$sql="select `".tblPrefix."associations`.`id` from `".tblPrefix."associations` where `id`=".$associationId;
				$res=MAIN::returnArray($sql);
		        if(count($res)>0){
		        	echo("association exists\n");
					ASSOCIATIONS::setAssociation($associationId,$DATA);
		        }else{
		        	echo("new association\n");
					$associationId = ASSOCIATIONS::setAssociation($associationId,$DATA);
		        }
				/*CIRCLE*/
				$circleId = mysql_real_escape_string($DATA['txt_circle_id']);
				$sql="select `".tblPrefix."circles`.`id` from `".tblPrefix."circles` where `id`=".$circleId;
				$res=MAIN::returnArray($sql);
		        if(count($res)>0){
		        	echo("circle exists\n");
					CIRCLES::setCircle($circleId,$DATA);
		        }else{
		        	echo("new circle\n");
					$circleId = CIRCLES::setCircle($circleId,$DATA);
		        }
				$personId =$leaderId= $DATA['txt_leader_id'];
				$sql="select `".tblPrefix."persons`.`id` from `".tblPrefix."persons` where `id`=".$personId;
				$res=MAIN::returnArray($sql);
		        if(count($res)>0){
		        	echo("person exists\n");
		        	$DATA['txt_user_id']=mysql_real_escape_string($DATA['txt_user_id']);
			    	$DATA['txt_person_firstname']=mysql_real_escape_string($DATA['txt_leader_firstname']);
			    	$DATA['txt_person_lastname']=mysql_real_escape_string($DATA['txt_leader_lastname']);
					$DATA['txt_person_pers']=mysql_real_escape_string($DATA['txt_leader_pers']);
			    	$DATA['txt_person_address']=mysql_real_escape_string($DATA['txt_leader_address']);
			    	$DATA['txt_person_zip']=mysql_real_escape_string($DATA['txt_leader_zip']);
			    	$DATA['txt_person_city']=mysql_real_escape_string($DATA['txt_leader_city']);
			    	$DATA['txt_person_tel']=mysql_real_escape_string($DATA['txt_leader_tel']);
					$DATA['txt_person_email']=mysql_real_escape_string($DATA['txt_leader_email']);
					if($leaderId = PERSONS::setPerson($DATA['txt_leader_id'],$DATA)){
						CIRCLES::setCircleLeader($circleId,$leaderId);				
					};
		        }else{
		        	echo("new person\n");
		        	$DATA['txt_user_id']=0;
			    	$DATA['txt_person_firstname']=mysql_real_escape_string($DATA['txt_leader_firstname']);
			    	$DATA['txt_person_lastname']=mysql_real_escape_string($DATA['txt_leader_lastname']);
					$DATA['txt_person_pers']=mysql_real_escape_string($DATA['txt_leader_pers']);
			    	$DATA['txt_person_address']=mysql_real_escape_string($DATA['txt_leader_address']);
			    	$DATA['txt_person_zip']=mysql_real_escape_string($DATA['txt_leader_zip']);
			    	$DATA['txt_person_city']=mysql_real_escape_string($DATA['txt_leader_city']);
			    	$DATA['txt_person_tel']=mysql_real_escape_string($DATA['txt_leader_tel']);
					$DATA['txt_person_email']=mysql_real_escape_string($DATA['txt_leader_email']);
					if($leaderId = PERSONS::setPerson($DATA['txt_leader_id'],$DATA)){
						CIRCLES::setCircleLeader($circleId,$leaderId);				
					};
		        }				
				for($i=0; $i<100;$i++){
					if(isset($DATA['txt_participant_id_'.$i])){
						$participantId = $DATA['txt_participant_id_'.$i];
						//echo $sql="select `".tblPrefix."persons`.`id` from `".tblPrefix."persons` where `id`=".$participantId;
						//$res=MAIN::returnArray($sql);
						
				        	$firstName = mysql_real_escape_string($DATA['txt_participant_firstname_'.$i]);
							$lastName = mysql_real_escape_string($DATA['txt_participant_lastname_'.$i]);
							$address = mysql_real_escape_string($DATA['txt_participant_address_'.$i]);
							$pers = mysql_real_escape_string($DATA['txt_participant_pers_'.$i]);
							$zip = mysql_real_escape_string($DATA['txt_participant_zip_'.$i]);
							$city = mysql_real_escape_string($DATA['txt_participant_city_'.$i]);
							$tel = mysql_real_escape_string($DATA['txt_participant_tel_'.$i]);
							if($participantId==0){
					        	$sql = "insert into `".tblPrefix."persons` (`user_id`,`firstname`,`lastname`,`address`,`pers`,
														`zip`,`city`,`tel`,`association_id`,`created_on`,`modif`)
												 values (0,'".$firstName."','".$lastName."','".$address."','".$pers."',
												 		'".$zip."','".$city."','".$tel."',".$associationId.",NOW(),NOW())";
								MAIN::executeCommand($sql);
								$memberId=mysql_insert_id();
								array_push($members,$memberId);
							}else{
								$sql = "update `".tblPrefix."persons` set 
								`firstname`='".$firstName."',`lastname`='".$lastName."',`address`='".$address."',`zip`='".$zip."',
								`city`='".$city."',`tel`='".$tel."',`pers`='".$pers."', 
								`modif`=NOW() where `id`=".$participantId;
								MAIN::executeCommand($sql);
								$memberId=$participantId;
								array_push($members,$memberId);
							}
					}
				}
				$members=implode(",",$members);
		    	$id=mysql_real_escape_string($id);
		    	$sql="select `".tblPrefix."registrations`.`id` from `".tblPrefix."registrations` where `id`=".$id;
				$res=MAIN::returnArray($sql);
		        if(count($res)>0){
		        	echo("registration exists");
					$sql="update `".tblPrefix."registrations` set
					`reg_date`=NOW(),`semester`='".$semester."',`association_id`=".$associationId.",`circle_id`=".$circleId.",
					`leader_id`=".$leaderId.",`members`='".$members."'
					WHERE `".tblPrefix."registrations`.`id`=".$id;
		        }else{        	
		        	echo("new registration");
		        	$sql="INSERT into `".tblPrefix."registrations`
		        	(`type`, `reg_date`,`semester`,`association_id`,`circle_id`,`leader_id`,`members`,`status`,`created_by`,`created_on`)
		        	VALUES
		        	('".$type."',NOW(),'".$semester."',".$associationId.",".$circleId.",".$leaderId.",'".$members."',1,".MAIN::decode($_SESSION[svar]).",NOW())";
		        }
				MAIN::executeCommand($sql);
				if($id==0){
					$registrationId=mysql_insert_id();
					$desc="New REGISTRATION";
				}else{
					$registrationId = $id;
					$desc="REGISTRATION Update";
				}
				LOGS::setLog('REGISTRATIONS', $desc, $registrationId, MAIN::getIP());    	
				return $registrationId;
			break;
			case 'Kulturprogram';
				$programs=array();
				/*ASSOCIATION*/
		    	$associationId = mysql_real_escape_string($DATA['txt_association_id']);
				$sql="select `".tblPrefix."associations`.`id` from `".tblPrefix."associations` where `id`=".$associationId;
				$res=MAIN::returnArray($sql);
		        if(count($res)>0){
		        	echo("association exists\n");
					ASSOCIATIONS::setAssociation($associationId,$DATA);
		        }else{
		        	echo("new association\n");
					$associationId = ASSOCIATIONS::setAssociation($associationId,$DATA);
		        }
		        $personId =$leaderId= $DATA['txt_leader_id'];
				$sql="select `".tblPrefix."persons`.`id` from `".tblPrefix."persons` where `id`=".$personId;
				$res=MAIN::returnArray($sql);
		        if(count($res)>0){
		        	echo("person exists\n");
		        	$DATA['txt_user_id']=mysql_real_escape_string($DATA['txt_user_id']);;
			    	$DATA['txt_person_firstname']=mysql_real_escape_string($DATA['txt_leader_firstname']);
			    	$DATA['txt_person_lastname']=mysql_real_escape_string($DATA['txt_leader_lastname']);
					$DATA['txt_person_pers']=mysql_real_escape_string($DATA['txt_leader_pers']);
			    	$DATA['txt_person_address']=mysql_real_escape_string($DATA['txt_leader_address']);
			    	$DATA['txt_person_zip']=mysql_real_escape_string($DATA['txt_leader_zip']);
			    	$DATA['txt_person_city']=mysql_real_escape_string($DATA['txt_leader_city']);
			    	$DATA['txt_person_tel']=mysql_real_escape_string($DATA['txt_leader_tel']);
					$DATA['txt_person_email']=mysql_real_escape_string($DATA['txt_leader_email']);
					PERSONS::setPerson($DATA['txt_leader_id'],$DATA);
		        }else{
		        	echo("new person\n");
		        	$DATA['txt_user_id']=mysql_real_escape_string($DATA['txt_user_id']);;;
			    	$DATA['txt_person_firstname']=mysql_real_escape_string($DATA['txt_leader_firstname']);
			    	$DATA['txt_person_lastname']=mysql_real_escape_string($DATA['txt_leader_lastname']);
					$DATA['txt_person_pers']=mysql_real_escape_string($DATA['txt_leader_pers']);
			    	$DATA['txt_person_address']=mysql_real_escape_string($DATA['txt_leader_address']);
			    	$DATA['txt_person_zip']=mysql_real_escape_string($DATA['txt_leader_zip']);
			    	$DATA['txt_person_city']=mysql_real_escape_string($DATA['txt_leader_city']);
			    	$DATA['txt_person_tel']=mysql_real_escape_string($DATA['txt_leader_tel']);
					$DATA['txt_person_email']=mysql_real_escape_string($DATA['txt_leader_email']);
					$leaderId = PERSONS::setPerson($DATA['txt_leader_id'],$DATA);					
		        }
				for($i=0; $i<100;$i++){
					if(isset($DATA['txt_circle_id_'.$i])){
						/*CIRCLE*/
						$circleId = mysql_real_escape_string($DATA['txt_circle_id_'.$i]);
						$sql="select `".tblPrefix."circles`.`id` from `".tblPrefix."circles` where `id`=".$circleId;
						$res=MAIN::returnArray($sql);
						$name=mysql_real_escape_string($DATA['txt_circle_name_'.$i]);
						$type=mysql_real_escape_string($type);
						$topic=mysql_real_escape_string($DATA['txt_circle_topic_'.$i]);
						$material="";
						$startDate=mysql_real_escape_string($DATA['txt_circle_start_date_'.$i]);
						$start=mysql_real_escape_string($DATA['txt_circle_start_'.$i]);
						$end=mysql_real_escape_string($DATA['txt_circle_end_'.$i]);
						$numberOfParticipants=mysql_real_escape_string(@$DATA['txt_circle_number_of_participants_'.$i]);
						$numberOfMeetings=0;
						$place=mysql_real_escape_string($DATA['txt_circle_place_'.$i]);
						$zip="";
						$city="";
						$dayOfTheWeek="";
				        if(count($res)>0){
				        	echo("circle exists\n");
							$sql = "update `".tblPrefix."circles` set 
								`name`='".$name."',`type`='".$type."',`topic`='".$topic."',`material`='".$material."',
								`leader`='".$leaderId."',`start_date`='".$startDate."',`day_of_the_week`='".$dayOfTheWeek."',`start`='".$start."',`end`='".$end."',
								`number_of_participants`='".$numberOfParticipants."',`number_of_meetings`='".$numberOfMeetings."',
								`place`='".$place."',`zip`='".$zip."',`city`='".$city."'
								where `id`=".$circleId;
				        }else{
				        	echo("new circle\n");
							$sql = "insert into `".tblPrefix."circles` (`name`,`type`,`topic`,`material`,
        								`leader`,`start_date`,`day_of_the_week`,`start`,`end`,
        								`number_of_participants`,`number_of_meetings`,
										`place`,`zip`,`city`,`created_by`,`created_on`)
								 values ('".$name."','".$type."','".$topic."','".$material."',
								 		'".$leaderId."','".$startDate."','".$dayOfTheWeek."','".$start."','".$end."',
								 		'".$numberOfParticipants."','".$numberOfMeetings."',
								 		'".$place."','".$zip."','".$city."','".MAIN::decode($_SESSION[svar])."',NOW())";
				        }
						MAIN::executeCommand($sql);
						if($circleId==0){
							$zid=mysql_insert_id();
							$desc="New CIRCLE ".$zid;
						}else{
							$zid = $circleId;
							$desc="CIRCLE Update ".$zid;
						}
						array_push($programs,$zid);											
						LOGS::setLog('CIRCLE', $desc, $zid, MAIN::getIP());
						//participants
					}
				}
				$programs=implode(",",$programs);				
				$members="";
		    	$id=mysql_real_escape_string($id);
		    	$sql="select `".tblPrefix."registrations`.`id` from `".tblPrefix."registrations` where `id`=".$id;
				$res=MAIN::returnArray($sql);
		        if(count($res)>0){
		        	echo("registration exists");
					$sql="update `".tblPrefix."registrations` set
					`reg_date`=NOW(),`semester`='".$semester."',`association_id`=".$associationId.",`circle_id`=".$circleId.",
					`leader_id`=".$leaderId.",`members`='".$members."',`programs`='".$programs."'
					WHERE `".tblPrefix."registrations`.`id`=".$id;
		        }else{        	
		        	echo("new registration");
		        	$sql="INSERT into `".tblPrefix."registrations`
		        	(`type`, `reg_date`,`semester`,`association_id`,`circle_id`,`leader_id`,`members`,`programs`,`status`,`created_by`,`created_on`)
		        	VALUES
		        	('".$type."',NOW(),'".$semester."',".$associationId.",".$circleId.",".$leaderId.",'".$members."','".$programs."',1,".MAIN::decode($_SESSION[svar]).",NOW())";
		        }
				MAIN::executeCommand($sql);
				if($id==0){
					$registrationId=mysql_insert_id();
					$desc="New REGISTRATION";
				}else{
					$registrationId = $id;
					$desc="REGISTRATION Update";
				}
				LOGS::setLog('REGISTRATIONS', $desc, $registrationId, MAIN::getIP());    	
				return $registrationId;
			break;
    	}
    	
    }	
	/** Function returning all registrationss in database 
	 * @param : order by, order order
	 * @return: array (registrations)
	 */
    function getRegistrations($orderBy='name',$ord='asc'){
    	$tmpUser = USERS::getUser(-1);
    	if($tmpUser['grouplevel']<50){     	    	
		$sql = "select `".tblPrefix."registrations`.*,
			`".tblPrefix."circles`.`name` as 'circle_name',
			CONCAT(`".tblPrefix."persons`.`firstname`,' ',`".tblPrefix."persons`.`lastname`) as 'leader_name'
		        from `".tblPrefix."registrations`
		        LEFT JOIN `".tblPrefix."circles` ON `".tblPrefix."registrations`.`circle_id`=`".tblPrefix."circles`.`id`
		        LEFT JOIN `".tblPrefix."persons` ON `".tblPrefix."registrations`.`leader_id`=`".tblPrefix."persons`.`id`		         
		        ORDER BY `".$orderBy."` ".$ord;
		}else{
			$sql="select `".tblPrefix."registrations`.*,
			`".tblPrefix."circles`.`name` as 'circle_name',
			CONCAT(`".tblPrefix."persons`.`firstname`,' ',`".tblPrefix."persons`.`lastname`) as 'leader_name'
		        from `".tblPrefix."registrations`
		        LEFT JOIN `".tblPrefix."circles` ON `".tblPrefix."registrations`.`circle_id`=`".tblPrefix."circles`.`id`
		        LEFT JOIN `".tblPrefix."persons` ON `".tblPrefix."registrations`.`leader_id`=`".tblPrefix."persons`.`id`
		        WHERE `".tblPrefix."registrations`.`leader_id`='".$tmpUser['person_id']."'
		        ORDER BY `".$orderBy."` ".$ord;
		}
		if($tmpUser['grouplevel']==50){
			$res=array();	
			$associations = ASSOCIATIONS::getAssociations();
			foreach($associations as $ass){
				$sql = "select `".tblPrefix."registrations`.*,
					`".tblPrefix."circles`.`name` as 'circle_name',
					CONCAT(`".tblPrefix."persons`.`firstname`,' ',`".tblPrefix."persons`.`lastname`) as 'leader_name'
				        from `".tblPrefix."registrations`
				        LEFT JOIN `".tblPrefix."circles` ON `".tblPrefix."registrations`.`circle_id`=`".tblPrefix."circles`.`id`
				        LEFT JOIN `".tblPrefix."persons` ON `".tblPrefix."registrations`.`leader_id`=`".tblPrefix."persons`.`id`
				        WHERE `".tblPrefix."registrations`.`association_id`='".$ass['id']."'
				        ORDER BY `".$orderBy."` ".$ord;
				$regs=MAIN::returnArray($sql);
				foreach($regs as $reg){
					array_push($res,$reg);
				}				
			}			
			return $res;
		}
		return MAIN::returnArray($sql);
	}
	/**
	 * Function for removing user data from database
	 * @param : $registrationsId
	 * @return: void
	 */
    function delRegistration($registrationId){
    	$sql="delete from `".tblPrefix."registrations` where `id`=".$registrationId;    	
        LOGS::setLog('REGISTRATION', "REGISTRATION Delete", $registrationId, MAIN::getIP());
		return MAIN::executeCommand($sql);
    }
	/**
	 * Function toggle status
	 * @param : $registrationsId, $status
	 * @return: void	
	 */
	function toggleRegistrartionStatus($registrationId, $status){
		$sql="update `".tblPrefix."registrations` set `status`=".$status." Where `id`=".$registrationId;
		return MAIN::executeCommand($sql);
	}
	/**
	 * Function getting the studyPlan attached to the registration
	 * @param : registrationId
	 * @return : array
	 */
	 function getStudyPlan($regId){
	 	$sql="select * from `".tblPrefix."studyplan` where `".tblPrefix."studyplan`.`reg_id`=".mysql_real_escape_string($regId);
		$res = MAIN::returnArray($sql);
		if(count($res)>0){			
			return $res[0];
		}else{
			$sql="describe `".tblPrefix."studyplan`";
    		$tmpres=MAIN::returnArray($sql);
			$res=array();
    		foreach($tmpres as $r){    			
    			if($r['Field']=='totalweeks' || $r['Field']=='totalmeetings' || $r['Field']=='hoursmeeting'){
    				$res[0][$r['Field']]=0;
    			}else{
    				$res[0][$r['Field']]='';
    			}
    		}			
			return $res[0];
		}
	 }
	 /**
	 * Function setting the studyPlan attached to the registration
	 * @param : POST DATA
	 * @return : registration ID
	 */
	 function setStudyPlan($DATA){
	 	$regId = mysql_real_escape_string($DATA['txt_s_reg_id']);
		$namedescription = mysql_real_escape_string($DATA['txt_s_namedescription']);
		$studymaterial = mysql_real_escape_string($DATA['txt_s_studymaterial']);
		$jobplan = mysql_real_escape_string($DATA['txt_s_jobplan']);
		$totalweeks = mysql_real_escape_string($DATA['txt_s_totalweeks']);
		$totalmeetings = mysql_real_escape_string($DATA['txt_s_totalmeetings']);
		$hoursmeeting = mysql_real_escape_string($DATA['txt_s_hoursmeeting']);
		$goal = mysql_real_escape_string($DATA['txt_s_goal']);
		//check if exists
		$sql="select `".tblPrefix."studyplan`.`id` from `".tblPrefix."studyplan` where `".tblPrefix."studyplan`.`reg_id`=".$regId;
		$res=MAIN::returnArray($sql);
		if(count($res)>0){
			echo $sql="update `".tblPrefix."studyplan`
			SET
			`namedescription`='".$namedescription."',
			`studymaterial`='".$studymaterial."',
			`jobplan`='".$jobplan."',
			`totalweeks`='".$totalweeks."',
			`totalmeetings`='".$totalmeetings."',
			`hoursmeeting`='".$hoursmeeting."',
			`goal`='".$goal."'
			WHERE `".tblPrefix."studyplan`.`reg_id`=".$regId;
		}else{
			echo $sql="INSERT INTO `".tblPrefix."studyplan`
			(`reg_id`,`namedescription`,`studymaterial`,`jobplan`,`totalweeks`,`totalmeetings`,`hoursmeeting`,`goal`,`user_id`)
			VALUES
			('".$regId."','".$namedescription."','".$studymaterial."','".$jobplan."','".$totalweeks."','".$totalmeetings."','".$hoursmeeting."','".$goal."','".MAIN::decode($_SESSION[svar])."')";
		}
		MAIN::executeCommand($sql);
		return $regId;
	 }
	/**
	 * Function for checking if registration has studyplan
	 * @param : registrationId
	 * @return : boolean
	 */
	 function isStudyPlan($regId){
	 	$sql="select * from `".tblPrefix."studyplan` where `".tblPrefix."studyplan`.`reg_id`=".mysql_real_escape_string($regId);
		$res = MAIN::returnArray($sql);
		if(count($res)>0){			
			return true;
		}else{
			return false;
		}
	 }
}
?>